//
//  LzwSingleton.m
//  NewHalfsugar
//
//  Created by 刘志武 on 16/1/8.
//  Copyright © 2016年 刘志武. All rights reserved.
//

#import "LzwSingleton.h"
#import <sqlite3.h>
@implementation LzwSingleton

+(instancetype)shareDataBase{
    
    static LzwSingleton *dataHandle = nil;
    
    
    if (dataHandle == nil) {
        dataHandle = [[LzwSingleton alloc]init];
    }
//    dispatch_once_t onceToken;
//    
//    dispatch_once(&onceToken, ^{
//        
//        dataHandle = [[LzwSingleton alloc]init];
//        
//    });
    
    return dataHandle;
    
}

// 创建一个数据库对象
static sqlite3 *db = nil;

// 打开数据库
- (void)openDB{
    
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    // 拼一个数据库文件路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *filePath = [documentPath stringByAppendingPathComponent:@"bantang.sqlite"];
    
    NSLog(@"filePath = %@", filePath);
    
    // 打开数据库
    BOOL result = sqlite3_open(filePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功");
    }else {
        NSLog(@"打开数据库失败");
    }
    
}
// 创建首页第一个表
- (void)createTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS bantang2016(number INTEGER PRIMARY KEY AUTOINCREMENT, photo TEXT)";
    
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功");
    }else {
        NSLog(@"创建失败");
    }
}
//创建首页第二个表
-(void)createNewBetweenTable{
    // 创建SQL语句
    // number声明成了主键, 系统自动生成键值
    // lanou1028:表名
    NSString *sql = @"CREATE TABLE IF NOT EXISTS bantangBetween2016(number INTEGER PRIMARY KEY AUTOINCREMENT, photo TEXT, extend TEXT)";
    
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建第二个表成功");
    }else {
        NSLog(@"创建第二个表失败");
    }
}
//创建首页第三个表
-(void)createNewFootTable{
    // 创建SQL语句
    // number声明成了主键, 系统自动生成键值
    // lanou1028:表名
    NSString *sql = @"CREATE TABLE IF NOT EXISTS bantangnew2016(number INTEGER PRIMARY KEY AUTOINCREMENT, pic TEXT, title TEXT, likes TEXT, id TEXT)";
    
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建第三个表成功");
    }else {
        NSLog(@"创建第三个表失败");
    }
    
    
}

//插入首页轮播图对象
-(void)insertLzwNewHeadIntoTable:(LzwNewHeadModle *)newHead{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO bantang2016(photo) VALUES('%@')", newHead.photo];
    
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入轮播图成功");
    }else {
        NSLog(@"插入轮播图失败");
    }
    
}
//插入中间滚动对象
-(void)insertLzwNewBetweenIntoTable:(LzwNewBetweenModle *)newBetween{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO bantangBetween2016(photo, extend) VALUES('%@', '%@')", newBetween.photo, newBetween.extend];
    
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入中间滚动视图成功");
    }else {
        NSLog(@"插入中间滚动视图失败");
    }
    
}
//插入下面的视图图像
-(void)insertLzwModleNewTwoIntoTable:(LzwModleNewTwo *)newFoot{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO bantangnew2016(pic, title, likes, id) VALUES('%@', '%@', '%@', '%@')", newFoot.pic, newFoot.title, newFoot.likes, newFoot.id];
    
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入下面视图成功");
    }else {
        NSLog(@"插入下面视图失败");
    }
    
}

//创建首页查询表1
-(NSMutableArray *)selectNewHeaderTable{
    
    //数组存modle
    NSMutableArray *array = [NSMutableArray array];
    //查询所有
    NSString *sql = @"SELECT * FROM bantang2016";
    //    //查询指定条件
    //    NSString *sql = [NSString stringWithFormat:@"SELECT *FROM lanou1028 WHERE age = '%ld'",stu.age];
    //创建数据库跟随指针,主要用于遍历查询表中每一行上面的数据
    sqlite3_stmt *stmt = nil;
    
    //准备查询工作
    
    //参数 -1 代表sql语句不限长度
    
    
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"查询首页轮播图准备成功");
    }else{
        NSLog(@"查询首页轮播图准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //读取某行上面的第二列数据
        const unsigned char *photo =  sqlite3_column_text(stmt, 1);
        
        //强转类型
        NSString *photo1 = [NSString stringWithUTF8String:(const char *)photo];
        
        LzwNewHeadModle *modleH = [[LzwNewHeadModle alloc]init];
        modleH.photo = photo1;
        
        
        [array addObject:modleH];
    }
    return array;
    
}
//创建首页查询表2
- (NSMutableArray *)selectNewBetweenTable{
    //数组存modle
    NSMutableArray *array = [NSMutableArray array];
    
    
    
    //查询所有
    NSString *sql = @"SELECT * FROM bantangBetween2016";
    
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"查询中间视图准备成功");
    }else{
        NSLog(@"查询中间视图准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //读取某行上面的第二列数据
        const unsigned char *charName =  sqlite3_column_text(stmt, 1);
        const unsigned char *charName1 =  sqlite3_column_text(stmt, 2);
        
        //强转类型
        NSString *name = [NSString stringWithUTF8String:(const char *)charName];
        
        NSString *name1 = [NSString stringWithUTF8String:(const char *)charName1];
        LzwNewBetweenModle *modleB = [[LzwNewBetweenModle alloc]init];
        modleB.photo = name;
        modleB.extend = name1;
        
        [array addObject:modleB];
    }
    return array;
    
}
//创建首页查询表3
- (NSMutableArray *)selectNewFooterTable{
    //数组存modle
    NSMutableArray *array = [NSMutableArray array];
    
    //查询所有
    NSString *sql = @"SELECT * FROM bantangnew2016";
    
    sqlite3_stmt *stmt = nil;
    
    //准备查询工作
    
    //参数 -1 代表sql语句不限长度
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"查询下面准备成功");
    }else{
        NSLog(@"查询下面准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //读取某行上面的第二列数据
        const unsigned char *charName =  sqlite3_column_text(stmt, 1);
        const unsigned char *charName1 =  sqlite3_column_text(stmt, 2);
        const unsigned char *charName2 =  sqlite3_column_text(stmt, 3);
        const unsigned char *charName3 =  sqlite3_column_text(stmt, 4);
        
        //强转类型
        NSString *name = [NSString stringWithUTF8String:(const char *)charName];
        NSString *name1 = [NSString stringWithUTF8String:(const char *)charName1];
        NSString *name2 = [NSString stringWithUTF8String:(const char *)charName2];
        NSString *name3 = [NSString stringWithUTF8String:(const char *)charName3];
        
        LzwModleNewTwo *modleTwo = [[LzwModleNewTwo alloc]init];
        modleTwo.pic = name;
        modleTwo.title = name1;
        modleTwo.likes = name2;
        modleTwo.id = name3;
        [array addObject:modleTwo];
    }
    return array;
}



//创建关注表
-(void)createAttentionTable{
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS Attention1028(number INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, avatar TEXT, share_url TEXT)";
    
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建关注表成功");
    }else {
        NSLog(@"创建关注表失败");
    }
}
//创建插入关注对象
- (void)insertAttentionIntoTable:(LzwModleSmall *)modleS{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Attention1028(content, avatar, share_url) VALUES('%@', '%@', '%@')", modleS.content, modleS.avatar, modleS.share_url];
    
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"插入关注对象成功");
    }else {
        NSLog(@"插入关注对象失败");
    }
    
}
//创建查询关注表
-(NSMutableArray *)selectAttentionTable{
    
    //数组存modle
    NSMutableArray *array = [NSMutableArray array];
    //查询所有
    NSString *sql = @"SELECT * FROM Attention1028";
    
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }else{
        NSLog(@"查询准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //读取某行上面的第二列数据
        const unsigned char *charName =  sqlite3_column_text(stmt, 1);
        const unsigned char *charName1 =  sqlite3_column_text(stmt, 2);
        const unsigned char *charName2 =  sqlite3_column_text(stmt, 3);
        
        //强转类型
        NSString *name = [NSString stringWithUTF8String:(const char *)charName];
        NSString *name1 = [NSString stringWithUTF8String:(const char *)charName1];
        NSString *name2 = [NSString stringWithUTF8String:(const char *)charName2];
        
        LzwModleSmall *modleSs = [[LzwModleSmall alloc]init];
        modleSs.content = name;
        
        //        LzwModleSmall *modleS1 = [[LzwModleSmall alloc]init];
        modleSs.avatar = name1;
        modleSs.share_url = name2;
        
        [array addObject:modleSs];
    }
    return array;
    
    
}
//创建删除关注的表
-(void)deleteAttentionTable:(NSString *)content{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM Attention1028 WHERE content = '%@'", content];
    
    BOOL resule = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (resule == SQLITE_OK) {
        NSLog(@"删除活动成功");
    }else{
        NSLog(@"删除活动失败");
    }
}

//创建第二页数据缓存表
//上面表
-(void)createNewAdressHeadTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS bantangAdressHead(number INTEGER PRIMARY KEY AUTOINCREMENT, pic1 TEXT)";
    
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建第二页头部表成功");
    }else {
        NSLog(@"创建第二页头部表失败");
    }
    
    
}
//下面表
- (void)createNewAdressFootTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS bantangAdressFoot(number INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT)";
    
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建第二页头部以下表成功");
    }else {
        NSLog(@"创建第二页头部以下表失败");
    }
}

//创建第二页上面插入表
- (void)insertLzwAdressHeadIntoTable:(LzwAdressModle *)modleA{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO bantangAdressHead(pic1) VALUES('%@')", modleA.pic1];
    
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入第二页头部视图成功");
    }else {
        NSLog(@"插入第二页头部视图失败");
    }
    
}
- (void)insertLzwNewAdressFootIntoTable:(LzwModleSmall *)modleB{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO bantangAdressFoot(content) VALUES('%@')", modleB.content];
    
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入第二页头部以下视图成功");
    }else {
        NSLog(@"插入第二页头部以下视图失败");
    }
    
}

//创建第二页头部视图查询表
- (NSMutableArray *)selectNewAdressHeadTable{
    
    //数组存modle
    NSMutableArray *array = [NSMutableArray array];
    
    //查询所有
    NSString *sql = @"SELECT * FROM bantangAdressHead";
    
    sqlite3_stmt *stmt = nil;
    
    //准备查询工作
    
    //参数 -1 代表sql语句不限长度
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"查询第二页头部视图准备成功");
    }else{
        NSLog(@"查询第二页头部视图准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //读取某行上面的第二列数据
        const unsigned char *charName =  sqlite3_column_text(stmt, 1);
        
        
        //强转类型
        NSString *name = [NSString stringWithUTF8String:(const char *)charName];
        
        
        LzwAdressModle *modleAdd = [[LzwAdressModle alloc]init];
        modleAdd.pic1 = name;
        
        [array addObject:modleAdd];
    }
    return array;
    
}
//创建第二页头部以下视图查询表
-(NSMutableArray *)selectNewAdressFootTable{
    
    //数组存modle
    NSMutableArray *array = [NSMutableArray array];
    
    //查询所有
    NSString *sql = @"SELECT * FROM bantangAdressFoot";
    
    sqlite3_stmt *stmt = nil;
    
    //准备查询工作
    
    //参数 -1 代表sql语句不限长度
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"查询第二页下面准备成功");
    }else{
        NSLog(@"查询第二页下面准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //读取某行上面的第二列数据
        
        const unsigned char *charName1 =  sqlite3_column_text(stmt, 1);
        
        
        
        //强转类型
        
        NSString *name1 = [NSString stringWithUTF8String:(const char *)charName1];
        
        
        
        LzwModleSmall *modleS = [[LzwModleSmall alloc]init];
        modleS.content = name1;
        
        [array addObject:modleS];
    }
    return array;
    
    
    
}

//创建第三页数据缓存的表
-(void)creatBellTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS bantangBell2016(number INTEGER PRIMARY KEY AUTOINCREMENT, photo TEXT, title TEXT, extend TEXT)";
    
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建第三页表成功");
    }else {
        NSLog(@"创建第三页表失败");
    }
    
}
-(void)insertLzwBellTable:(LzwBellModle *)modleBell{
    
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO bantangBell2016(photo, title, extend) VALUES('%@', '%@', '%@')", modleBell.photo, modleBell.title, modleBell.extend];
    
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入第三页头部视图成功");
    }else {
        NSLog(@"插入第三页头部视图失败");
    }
}

- (NSMutableArray *)selectBellTable{
    
    
    //数组存modle
    NSMutableArray *array = [NSMutableArray array];
    
    //查询所有
    NSString *sql = @"SELECT * FROM bantangBell2016";
    
    sqlite3_stmt *stmt = nil;
    
    //准备查询工作
    
    //参数 -1 代表sql语句不限长度
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    
    if (result == SQLITE_OK) {
        NSLog(@"查询第三页准备成功");
    }else{
        NSLog(@"查询第三页准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //读取某行上面的第二列数据
        
        const unsigned char *charName1 =  sqlite3_column_text(stmt, 1);
        const unsigned char *charName2 =  sqlite3_column_text(stmt, 2);
        
        const unsigned char *charName3 =  sqlite3_column_text(stmt, 3);
        
        //强转类型
        
        NSString *name1 = [NSString stringWithUTF8String:(const char *)charName1];
        
        NSString *name2 = [NSString stringWithUTF8String:(const char *)charName2];
        NSString *name3 = [NSString stringWithUTF8String:(const char *)charName3];
        
        LzwBellModle *modleS = [[LzwBellModle alloc]init];
        modleS.photo = name1;
        modleS.title = name2;
        modleS.extend = name3;
        
        [array addObject:modleS];
    }
    return array;
    
}
//删除收藏表
- (void)deleteBellCollect:(NSString *)title{
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM bantangBell2016 WHERE title = '%@'", title];
    
    BOOL resule = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (resule == SQLITE_OK) {
        NSLog(@"删除活动成功");
    }else{
        NSLog(@"删除活动失败");
    }
    
    
}
//删除yi表
-(void)deleteFirstTable:(NSString *)photo{
    
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM bantang2016 WHERE photo = '%@'", photo];
    
    BOOL resule = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (resule == SQLITE_OK) {
        NSLog(@"删除轮播图成功");
    }else{
        NSLog(@"删除轮播图失败");
    }
    
}
//删除二张表
-(void)deleteTwoTable:(NSString *)photo{
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM bantangBetween2016 WHERE photo = '%@'", photo];
    
    BOOL resule = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (resule == SQLITE_OK) {
        NSLog(@"删除中间图成功");
    }else{
        NSLog(@"删除中间图失败");
    }
    
}
- (void)deleteThreeTable:(NSString *)title{
    
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM bantangnew2016 WHERE title = '%@'", title];
    
    BOOL resule = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (resule == SQLITE_OK) {
        NSLog(@"删除下面图成功");
    }else{
        NSLog(@"删除下面图失败");
    }
}

@end
